package com.pt.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.pt.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface UserMapper extends BaseMapper<User> {
    
    @Update("UPDATE user SET uploaded = uploaded + #{uploaded}, downloaded = downloaded + #{downloaded} WHERE id = #{userId}")
    int updateUserStats(@Param("userId") Long userId, @Param("uploaded") Long uploaded, @Param("downloaded") Long downloaded);
    
    @Update("UPDATE user SET bonus_points = bonus_points + #{points} WHERE id = #{userId}")
    int updateBonusPoints(@Param("userId") Long userId, @Param("points") Integer points);
    
    @Select("SELECT SUM(uploaded) as totalUploaded, SUM(downloaded) as totalDownloaded FROM user")
    Map<String, Object> getTotalTrafficStats();
    
    @Select("SELECT sub_date.date as date, " +
           "COALESCE(SUM(u.uploaded), 0) as upload, " +
           "COALESCE(SUM(u.downloaded), 0) as download " +
           "FROM (" +
           "  SELECT DATE_SUB(CURDATE(), INTERVAL (n.number - 1) DAY) as date " +
           "  FROM (" +
           "    SELECT 1 as number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 " +
           "    UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 " +
           "    UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 " +
           "    UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 " +
           "    UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 " +
           "    UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30 " +
           "    UNION SELECT 31" +
           "  ) n " +
           "  WHERE n.number <= #{days}" +
           ") sub_date " +
           "LEFT JOIN user u ON DATE(u.created_time) = sub_date.date " +
           "GROUP BY sub_date.date " +
           "ORDER BY sub_date.date DESC")
    List<Map<String, Object>> getDailyTrafficStats(@Param("days") int days);

    @Select("SELECT sub_date.date as date, " +
           "COALESCE(COUNT(DISTINCT CASE WHEN DATE(u.last_login_time) = sub_date.date THEN u.id END), 0) as activeUsers, " +
           "COALESCE(COUNT(DISTINCT CASE WHEN DATE(u.created_time) = sub_date.date THEN u.id END), 0) as newUsers " +
           "FROM (" +
           "  SELECT DATE_SUB(CURDATE(), INTERVAL (n.number - 1) DAY) as date " +
           "  FROM (" +
           "    SELECT 1 as number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 " +
           "    UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 " +
           "    UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 " +
           "    UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 " +
           "    UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 " +
           "    UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30 " +
           "    UNION SELECT 31" +
           "  ) n " +
           "  WHERE n.number <= #{days}" +
           ") sub_date " +
           "LEFT JOIN user u ON (DATE(u.last_login_time) = sub_date.date OR DATE(u.created_time) = sub_date.date) " +
           "GROUP BY sub_date.date " +
           "ORDER BY sub_date.date DESC")
    List<Map<String, Object>> getActiveUserStats(@Param("days") int days);

    /**
     * 获取用户发布的种子数量
     */
    @Select("SELECT COUNT(*) FROM torrent WHERE uploader_id = #{userId} AND is_deleted = 0")
    Integer getUserTorrentCount(@Param("userId") Long userId);

    /**
     * 获取用户正在做种的数量
     */
    @Select("SELECT COUNT(*) FROM peer WHERE user_id = #{userId} AND seeder = 1")
    Integer getUserSeedingCount(@Param("userId") Long userId);

    /**
     * 获取用户正在下载的数量
     */
    @Select("SELECT COUNT(*) FROM peer WHERE user_id = #{userId} AND seeder = 0")
    Integer getUserLeechingCount(@Param("userId") Long userId);

    /**
     * 获取用户可用邀请码数量
     */
    @Select("SELECT COUNT(*) FROM invitation WHERE creator_id = #{userId} AND status = 1")
    Integer getUserInviteCount(@Param("userId") Long userId);

    /**
     * 获取用户发布的帖子数量
     */
    @Select("SELECT COUNT(*) FROM forum_topic WHERE author_id = #{userId} AND is_deleted = 0")
    Integer getUserTopicCount(@Param("userId") Long userId);

    /**
     * 获取用户今日上传下载量
     */
    @Select("SELECT " +
            "COALESCE(SUM(CASE WHEN t.size > 0 THEN t.size ELSE 0 END), 0) as todayUpload, " +
            "COALESCE(SUM(CASE WHEN t.size > 0 THEN t.size ELSE 0 END), 0) as todayDownload " +
            "FROM torrent t " +
            "WHERE t.uploader_id = #{userId} AND DATE(t.created_time) = CURDATE()")
    Map<String, Object> getUserTodayStats(@Param("userId") Long userId);

    /**
     * 获取用户本周上传下载量
     */
    @Select("SELECT " +
            "COALESCE(SUM(CASE WHEN t.size > 0 THEN t.size ELSE 0 END), 0) as weekUpload, " +
            "COALESCE(SUM(CASE WHEN t.size > 0 THEN t.size ELSE 0 END), 0) as weekDownload " +
            "FROM torrent t " +
            "WHERE t.uploader_id = #{userId} AND YEARWEEK(t.created_time, 1) = YEARWEEK(CURDATE(), 1)")
    Map<String, Object> getUserWeekStats(@Param("userId") Long userId);

    /**
     * 获取用户本月上传下载量
     */
    @Select("SELECT " +
            "COALESCE(SUM(CASE WHEN t.size > 0 THEN t.size ELSE 0 END), 0) as monthUpload, " +
            "COALESCE(SUM(CASE WHEN t.size > 0 THEN t.size ELSE 0 END), 0) as monthDownload " +
            "FROM torrent t " +
            "WHERE t.uploader_id = #{userId} AND YEAR(t.created_time) = YEAR(CURDATE()) AND MONTH(t.created_time) = MONTH(CURDATE())")
    Map<String, Object> getUserMonthStats(@Param("userId") Long userId);

    /**
     * 基于日期范围的流量统计 - 真实数据版本
     */
    @Select("SELECT sub_date.date as date, " +
           "COALESCE(SUM(u.uploaded), 0) as upload, " +
           "COALESCE(SUM(u.downloaded), 0) as download " +
           "FROM (" +
           "  SELECT DATE_ADD(#{startDate}, INTERVAL n.number DAY) as date " +
           "  FROM (" +
           "    SELECT 0 as number UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 " +
           "    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 " +
           "    UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 " +
           "    UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 " +
           "    UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 " +
           "    UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 " +
           "    UNION SELECT 30 UNION SELECT 31 UNION SELECT 32 UNION SELECT 33 UNION SELECT 34 " +
           "    UNION SELECT 35 UNION SELECT 36 UNION SELECT 37 UNION SELECT 38 UNION SELECT 39 " +
           "    UNION SELECT 40 UNION SELECT 41 UNION SELECT 42 UNION SELECT 43 UNION SELECT 44 " +
           "    UNION SELECT 45 UNION SELECT 46 UNION SELECT 47 UNION SELECT 48 UNION SELECT 49 " +
           "    UNION SELECT 50 UNION SELECT 51 UNION SELECT 52 UNION SELECT 53 UNION SELECT 54 " +
           "    UNION SELECT 55 UNION SELECT 56 UNION SELECT 57 UNION SELECT 58 UNION SELECT 59 " +
           "    UNION SELECT 60" +
           "  ) n " +
           "  WHERE DATE_ADD(#{startDate}, INTERVAL n.number DAY) <= #{endDate}" +
           ") sub_date " +
           "LEFT JOIN user u ON DATE(u.created_time) = sub_date.date " +
           "GROUP BY sub_date.date " +
           "ORDER BY sub_date.date DESC")
    List<Map<String, Object>> getDailyTrafficStatsByDateRange(@Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 基于日期范围的活跃用户统计 - 真实数据版本
     */
    @Select("SELECT sub_date.date as date, " +
           "COALESCE(COUNT(DISTINCT CASE WHEN DATE(u.last_login_time) = sub_date.date THEN u.id END), 0) as activeUsers, " +
           "COALESCE(COUNT(DISTINCT CASE WHEN DATE(u.created_time) = sub_date.date THEN u.id END), 0) as newUsers " +
           "FROM (" +
           "  SELECT DATE_ADD(#{startDate}, INTERVAL n.number DAY) as date " +
           "  FROM (" +
           "    SELECT 0 as number UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 " +
           "    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 " +
           "    UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 " +
           "    UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 " +
           "    UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 " +
           "    UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 " +
           "    UNION SELECT 30 UNION SELECT 31 UNION SELECT 32 UNION SELECT 33 UNION SELECT 34 " +
           "    UNION SELECT 35 UNION SELECT 36 UNION SELECT 37 UNION SELECT 38 UNION SELECT 39 " +
           "    UNION SELECT 40 UNION SELECT 41 UNION SELECT 42 UNION SELECT 43 UNION SELECT 44 " +
           "    UNION SELECT 45 UNION SELECT 46 UNION SELECT 47 UNION SELECT 48 UNION SELECT 49 " +
           "    UNION SELECT 50 UNION SELECT 51 UNION SELECT 52 UNION SELECT 53 UNION SELECT 54 " +
           "    UNION SELECT 55 UNION SELECT 56 UNION SELECT 57 UNION SELECT 58 UNION SELECT 59 " +
           "    UNION SELECT 60" +
           "  ) n " +
           "  WHERE DATE_ADD(#{startDate}, INTERVAL n.number DAY) <= #{endDate}" +
           ") sub_date " +
           "LEFT JOIN user u ON (DATE(u.last_login_time) = sub_date.date OR DATE(u.created_time) = sub_date.date) " +
           "GROUP BY sub_date.date " +
           "ORDER BY sub_date.date DESC")
    List<Map<String, Object>> getActiveUserStatsByDateRange(@Param("startDate") String startDate, @Param("endDate") String endDate);
} 